﻿using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Data.SqlClient;

namespace DataUpload.ReadFiles
{
    public class DriverDrivenFile: ParsedFile
    {
        private string _connString;
        private OleDbConnection _connection;
        private DataSet _ds;
        private ArrayList _sourceCommands;
        private ArrayList _sColumns;
        private ArrayList _targetCommands_prefix;
        private ArrayList _tCommands_source;
        private ArrayList _tColumns_index;

        public string ConnectionString
        {
            get { return _connString; }
        }

        public DriverDrivenFile(string filePath)
            : base(filePath)
        {
            string ext = filePath.Substring(filePath.LastIndexOf("."));
            ext = ext.ToLower();
            switch (ext)
            {
                case ".xls":
                    _connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
                    break;
                case ".mdb":
                    _connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath;
                    break;
                case ".xlsx":
                    _connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0 Xml";
                    break;
                case ".mdf":
                    if (filePath.Contains("\\"))
                        filePath = filePath.Substring(filePath.LastIndexOf("\\") + 1);
                    filePath = filePath.Substring(0, filePath.LastIndexOf("."));
                    _connString = "Provider=SQLNCLI.1;Data Source=localhost;Initial Catalog=" + filePath + ";Integrated Security=SSPI";
                    //_connString = "Provider=SQLNCLI.1;Data Source=localhost;Initial File Name=" + filePath + ";Integrated Security=SSPI";
                    break;
                default:
                    throw new ArgumentException("Unsupport file extension! Please try manual connection string!");
            }

            try
            {
                _connection = new OleDbConnection(_connString);
                _connection.Open();
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Try to connect to " + _filePath + " using the connection string " + _connString, exc);
                throw new ArgumentException("Cannot connect to " + _filePath + ". Please try manual connection string!", exc);
            }
            finally
            {
                _connection.Close();
            }

            _ds = new DataSet();
        }

        public DriverDrivenFile(string connString, bool manual)
        {
            _connString = connString;

            try
            {
                _connection = new OleDbConnection(_connString);
                _connection.Open();
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Try to connect using the connection string: " + _connString, exc);
                throw new ArgumentException("Cannot connect using " + connString, exc);
            }
            finally
            {
                _connection.Close();
            }

            if (_connString.IndexOf(";", _connString.IndexOf("Data Source=")) > 0)
                _filePath = _connString.Substring(_connString.IndexOf("Data Source=") + 12,
                    _connString.IndexOf(";", _connString.IndexOf("Data Source=")) - _connString.IndexOf("Data Source=") - 12);
            else
                _filePath = _connString.Substring(_connString.IndexOf("Data Source=") + 12,
                    _connString.Length - _connString.IndexOf("Data Source=") - 12);

            _ds = new DataSet();
        }

        ~DriverDrivenFile()
        {
            Clear();
        }

        #region Other functions
        public override string ToString()
        {
            return "DriverDrivenFile";
        }
        #endregion;

        #region Implement abstract functions
        public override ArrayList GetColumnsList()
        {
            try
            {
                _connection.Open();
                string[] restrictions = new string[4];
                restrictions[3] = "TABLE"; // where [TABLE_NAME] = 'TABLE'
                DataTable tables = _connection.GetSchema("TABLES", restrictions);
                restrictions = new string[3];
                DataTable columns;
                for (int i = 0; i < tables.Rows.Count; i++)
                {
                    string tb = tables.Rows[i][2].ToString();
                    if (tb != "sysdiagrams")
                    {
                        restrictions[2] = tb;
                        columns = _connection.GetSchema("COLUMNS", restrictions);
                        for (int j = 0; j < columns.Rows.Count; j++)
                        {
                            _columns.Add(tb + "." + columns.Rows[j][3].ToString());
                        }
                    }

                }
                return _columns;
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Attempting get list of columns in " + _filePath, exc);
                throw new Exception("Cannot get list of columns", exc);
            }
            finally
            {
                _connection.Close();
            }
        }

        public override bool Clear()
        {
            if (_columns != null)
                _columns.Clear();
            if (_rowsFailed != null)
                _rowsFailed.Clear();
            if (_sourceColumns != null)
                _sourceColumns.Clear();
            if (_targetColumns != null)
                _targetColumns.Clear();
            if (_targetType != null)
                _targetType.Clear();
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
            return true;
        }

        public override void Dispose()
        {
            Clear();
        }

        public override SortedList TakeSample(ArrayList columns, int numSample)
        {
            if (numSample == 0)
                return new SortedList();

            columns.Sort();
            for (int i = 0; i < columns.Count - 1; i++)
            {
                while (i + 1 < columns.Count && columns[i + 1].ToString() == columns[i].ToString())
                    columns.RemoveAt(i + 1);
            }

            SortedList result = new SortedList();
            int columnIndex;
            foreach (string c in columns)
            {
                columnIndex = _columns.IndexOf(c);
                if (!result.ContainsKey(columnIndex))
                {
                    ArrayList columnSample = new ArrayList();
                    result.Add(columnIndex, columnSample);
                }
            }

            string sourceTbName = columns[0].ToString().Substring(0, columns[0].ToString().IndexOf("."));
            string sqlCommand;
            int startIndex = 0, countSample = 0;
            OleDbCommand cmd;
            string action = "Opening connection to source data";
            try
            {
                _connection.Open();
                for (int i = 0; i < columns.Count; i++)
                {
                    if (columns[i].ToString().Substring(0, columns[i].ToString().IndexOf(".")) != sourceTbName)
                    {
                        sqlCommand = "select ";
                        for (int j = startIndex; j < i; j++)
                            sqlCommand += "[" + columns[j].ToString().Substring(columns[j].ToString().IndexOf(".") + 1) + "],";
                        sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
                        sqlCommand += " from [" + sourceTbName + "]";
                        
                        action = "Prepare to get source data";
                        cmd = new OleDbCommand(sqlCommand, _connection);
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            action = "Reading source data";
                            countSample = 0;
                            while (countSample <= numSample && reader.Read())
                            {
                                object[] values = new object[reader.FieldCount];
                                reader.GetValues(values);
                                countSample++;
                                action = "Inserting data into result SortedList";
                                for (int j = 0; j < values.Length; j++)
                                    ((ArrayList)result.GetByIndex(startIndex + j)).Add(values[j]);
                            }
                        }

                        sourceTbName = columns[i].ToString().Substring(0, columns[i].ToString().IndexOf("."));
                        startIndex = i;
                        i--;
                    }
                }

                sqlCommand = "select ";
                for (int j = startIndex; j < columns.Count; j++)
                    sqlCommand += "[" + columns[j].ToString().Substring(columns[j].ToString().IndexOf(".") + 1) + "],";
                sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
                sqlCommand += " from [" + sourceTbName + "]";
                
                action = "Prepare to get source data";
                cmd = new OleDbCommand(sqlCommand, _connection);
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    action = "Reading source data";
                    countSample = 0;
                    while (reader.Read())
                    {
                        object[] values = new object[reader.FieldCount];
                        reader.GetValues(values);
                        countSample++;
                        action = "Inserting data into result SortedList";
                        for (int j = 0; j < values.Length; j++)
                            ((ArrayList)result.GetByIndex(startIndex + j)).Add(values[j]);
                    }
                }
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis(action, exc);
                throw exc;
            }
            finally
            {
                _connection.Close();
            }

            return result;
        }

        public override void StartUpload(ref DBConnection.DBConnection connection)
        {
            if (_mapStatus != MapStatus.MappingDone)
                return;

            _rowsInserted = 0;
            _rowsFailed.Clear();
            _startUpload = DateTime.Now;
            _passRows = 0;
            TotalRows();

            string action = "Opening connection to source data";
            string sourceTbName;
            ArrayList targetCmds, sColumns;
            ArrayList scripts = new ArrayList();
            RowsInsertedEventArgs e;
            try
            {
                _connection.Open();
                foreach (string cmdText in _sourceCommands)
                {
                    sourceTbName = cmdText.Remove(0, cmdText.IndexOf("from [") + 6);
                    sourceTbName = sourceTbName.Substring(0, sourceTbName.IndexOf("]"));

                    targetCmds = new ArrayList();
                    sColumns = new ArrayList();
                    for (int i = 0; i < _tCommands_source.Count; i++)
                    {
                        if (_tCommands_source[i].ToString() == sourceTbName)
                        {
                            targetCmds.Add(_targetCommands_prefix[i]);
                            sColumns.Add(_sColumns[i]);
                        }
                    }

                    string[] sep = { ";" };
                    OleDbCommand cmd = new OleDbCommand(cmdText, _connection);
                    e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Generating scripts");
                    OnRowsInsertedEvent(this, e);
                    using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            ++_passRows;

                            if (_stopThis)
                            {
                                reader.Close();
                                reader.Dispose();
                                _endUpload = DateTime.Now;
                                return;
                            }

                            object[] values = new object[reader.FieldCount];
                            reader.GetValues(values);
                            System.Globalization.CultureInfo usCulture = new System.Globalization.CultureInfo("en-US");
                            for (int i = 0; i < targetCmds.Count; i++)
                            {
                                string script = targetCmds[i].ToString();
                                string[] indexes = sColumns[i].ToString().Split(sep, StringSplitOptions.RemoveEmptyEntries);
                                int[] types = (int[])_tColumns_index[i];
                                string aColumn;
                                for (int j = 0; j < indexes.Length; j++)
                                {
                                    aColumn = values[Int32.Parse(indexes[j])].ToString();
                                    try
                                    {
                                        if (_targetType[types[j]].ToString().ToLower() == "double")
                                        {
                                            double tmp = Double.Parse(aColumn, System.Globalization.NumberStyles.Any, _numFormat);
                                            aColumn = tmp.ToString(usCulture);
                                        }
                                        else if (_targetType[types[j]].ToString().ToLower() == "datetime")
                                        {
                                            DateTime tmp = DateTime.Parse(aColumn, _dateTimeFormat);
                                            aColumn = tmp.ToString("yyyy-M-d H:m:s");
                                        }
                                    }
                                    catch (Exception exc)
                                    {
                                        DBConnection.MyDebugClass.LogThis("Parsing value " + aColumn + " to " + _targetType[types[j]].ToString(), exc);
                                    }
                                    script += "'" + aColumn + "',";
                                }
                                script = script.Remove(script.Length - 1);
                                script += ")";
                                if (!scripts.Contains(script))
                                    scripts.Add(script);
                            }

                            if (scripts.Count > 19)
                            {
                                UploadThis(scripts, ref connection);
                                if (_stopThis)
                                {
                                    reader.Close();
                                    reader.Dispose();
                                    _endUpload = DateTime.Now;
                                    return;
                                }
                                scripts.Clear();
                                //scripts = new ArrayList(); --> which one is better? clear or new
                            }
                        }

                        UploadThis(scripts, ref connection);
                    }
                }
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis(action, exc);
                throw exc;
            }
            finally
            {
                _connection.Close();
                _endUpload = DateTime.Now;
            }
            _passRows = _totalRows;
            e = new RowsInsertedEventArgs(_rowsInserted, (uint)_rowsFailed.Count, "Uploading " + _filePath + ": Done");
            OnRowsInsertedEvent(this, e);
        }

        public override long TotalRows()
        {
            try
            {
                if (_connection.State == ConnectionState.Closed
                    || _connection.State == ConnectionState.Broken)
                    _connection.Open();

                string table;
                ArrayList tables = new ArrayList();
                foreach (string cmdText in _sourceCommands)
                {
                    table = cmdText.Remove(0, cmdText.IndexOf("from [") + 6);
                    table = table.Substring(0, table.IndexOf("]"));
                    if (!tables.Contains(table))
                        tables.Add(table);
                }

                foreach (string tb in tables)
                {
                    OleDbCommand cmd = new OleDbCommand("select count(*) from [" + tb + "]", _connection);
                    _totalRows += Int32.Parse((cmd.ExecuteScalar().ToString()));
                }
            }
            catch (Exception exc)
            {
                DBConnection.MyDebugClass.LogThis("Get total number of rows", exc);
                _totalRows = 0;
            }
            finally
            {
                _connection.Close();
            }
            return _totalRows;
        }
        #endregion;

        #region Override virtual functions
        protected override ParsedFile.MapStatus FirstCheckMap(ref ArrayList sourceColumns, ref ArrayList targetColumns, ref ArrayList targetType)
        {
            MapStatus status = base.FirstCheckMap(ref sourceColumns, ref targetColumns, ref targetType);
            if (status != MapStatus.MappingDone)
                return status;

            OrganizeMap(ref sourceColumns, ref targetColumns, ref targetType);

            string sourceTbName = sourceColumns[0].ToString().Substring(0, sourceColumns[0].ToString().IndexOf("."));
            string targetTbName = targetColumns[0].ToString().Substring(0, targetColumns[0].ToString().IndexOf("."));
            for (int i = 0; i < sourceColumns.Count; i++)
            {
                if (targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf(".")) == targetTbName)
                {
                    if (sourceColumns[i].ToString().Substring(0, sourceColumns[i].ToString().IndexOf(".")) != sourceTbName)
                    {
                        _mapStatus = MapStatus.SourceHasMultipleTables;
                        return MapStatus.SourceHasMultipleTables;
                    }
                }
                else
                {
                    sourceTbName = sourceColumns[i].ToString().Substring(0, sourceColumns[i].ToString().IndexOf("."));
                    targetTbName = targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf("."));
                }
            }

            _mapStatus = MapStatus.MappingDone;
            return _mapStatus;
        }

        protected override void OrganizeMap(ref ArrayList sourceColumns, ref ArrayList targetColumns, ref ArrayList targetType)
        {
            base.OrganizeMap(ref sourceColumns, ref targetColumns, ref targetType);

            ArrayList tmpSourceCols = new ArrayList();
            foreach (string s in sourceColumns)
            {
                if (tmpSourceCols.Contains(s) == false)
                    tmpSourceCols.Add(s);
            }
            tmpSourceCols.Sort();

            // Add source commands (these commands will retrieve data from current source).
            _sourceCommands = new ArrayList();
            string sqlCommand;
            string tbName = tmpSourceCols[0].ToString().Substring(0, tmpSourceCols[0].ToString().IndexOf("."));
            SortedList source_startIndexes = new SortedList();
            int startIndex = 0;
            source_startIndexes.Add(tbName, startIndex);
            for (int i = 0; i < tmpSourceCols.Count; i++)
            {
                if (tmpSourceCols[i].ToString().Substring(0, tmpSourceCols[i].ToString().IndexOf(".")) != tbName)
                {
                    sqlCommand = "select ";
                    for (int j = startIndex; j < i; j++)
                        sqlCommand += "[" + tmpSourceCols[j].ToString().Substring(tmpSourceCols[j].ToString().IndexOf(".") + 1) + "],";

                    sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
                    sqlCommand += " from [" + tmpSourceCols[startIndex].ToString().Substring(0, tmpSourceCols[startIndex].ToString().IndexOf(".")) + "]";
                    _sourceCommands.Add(sqlCommand);

                    startIndex = i;
                    tbName = tmpSourceCols[i].ToString().Substring(0, tmpSourceCols[i].ToString().IndexOf("."));
                    source_startIndexes.Add(tbName, startIndex);
                    i--;
                }
            }
            sqlCommand = "select ";
            for (int j = startIndex; j < tmpSourceCols.Count; j++)
                sqlCommand += "[" + tmpSourceCols[j].ToString().Substring(tmpSourceCols[j].ToString().IndexOf(".") + 1) + "],";

            sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
            sqlCommand += " from [" + tmpSourceCols[startIndex].ToString().Substring(0, tmpSourceCols[startIndex].ToString().IndexOf(".")) + "]";
            _sourceCommands.Add(sqlCommand);

            // Add target commands prefixes (these prefixes will be added data later).
            _targetCommands_prefix = new ArrayList();
            // contains source columns indexes for each target command.
            _sColumns = new ArrayList();
            // source table of each command
            _tCommands_source = new ArrayList();
            // index of type
            _tColumns_index = new ArrayList();
            ArrayList temp = new ArrayList();
            tbName = targetColumns[0].ToString().Substring(0, targetColumns[0].ToString().IndexOf("."));
            startIndex = 0;
            string indexes;
            int[] tmp;
            for (int i = 0; i < targetColumns.Count; i++)
            {
                if (targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf(".")) == tbName)
                    temp.Add(sourceColumns[i]);
                else
                {
                    sqlCommand = "insert into [" + tbName + "](";
                    tmp = new int[i - startIndex];
                    for (int j = startIndex; j < i; j++)
                    {
                        tmp[j - startIndex] = j;
                        sqlCommand += "[" + targetColumns[j].ToString().Substring(targetColumns[j].ToString().IndexOf(".") + 1) + "],";
                    }
                    _tColumns_index.Add(tmp);

                    sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
                    sqlCommand += ") values (";
                    _targetCommands_prefix.Add(sqlCommand);

                    indexes = "";
                    foreach (string s in temp)
                        indexes += (tmpSourceCols.IndexOf(s) - (int)source_startIndexes[temp[0].ToString().Substring(0, temp[0].ToString().IndexOf("."))]) + ";";
                    _sColumns.Add(indexes);
                    _tCommands_source.Add(temp[0].ToString().Substring(0, temp[0].ToString().IndexOf(".")));
                    temp = new ArrayList();

                    startIndex = i;
                    tbName = targetColumns[i].ToString().Substring(0, targetColumns[i].ToString().IndexOf("."));
                    i--;
                }
            }
            tmp = new int[targetColumns.Count - startIndex];
            sqlCommand = "insert into [" + tbName + "](";
            for (int j = startIndex; j < targetColumns.Count; j++)
            {
                tmp[j - startIndex] = j;
                sqlCommand += "[" + targetColumns[j].ToString().Substring(targetColumns[j].ToString().IndexOf(".") + 1) + "],";
            }
            _tColumns_index.Add(tmp);

            sqlCommand = sqlCommand.Remove(sqlCommand.Length - 1);
            sqlCommand += ") values (";
            _targetCommands_prefix.Add(sqlCommand);

            indexes = "";
            foreach (string s in temp)
                indexes += (tmpSourceCols.IndexOf(s) - (int)source_startIndexes[temp[0].ToString().Substring(0, temp[0].ToString().IndexOf("."))]) + ";";
            _sColumns.Add(indexes);
            _tCommands_source.Add(temp[0].ToString().Substring(0, temp[0].ToString().IndexOf(".")));
        }
        #endregion;

        #region Private functions
        private DataTable GetDataTable(string sqlCommand, string tbName)
        {
            if (_ds.Tables.IndexOf(tbName) != -1)
                _ds.Tables.Remove(tbName);

            try
            {
                if (_connection.State == ConnectionState.Closed
                    || _connection.State == ConnectionState.Broken)
                    _connection.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlCommand, _connection);
                adapter.Fill(_ds, tbName);
            }
            catch (Exception exc)
            {
                string action = "Executing the query: " + sqlCommand + " in " + _connection.DataSource + "." + _connection.Database;
                DBConnection.MyDebugClass.LogThis(action, exc);
                return null;
            }
            finally
            {
                _connection.Close();
            }

            return _ds.Tables[tbName];
        }
        #endregion;
    }
}
